package com.ztesoft.report.utils.excel;

import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.csvreader.CsvWriter;
import com.ztesoft.report.service.impl.ReportServiceImpl;
import com.ztesoft.report.utils.ExcelDataExport;
import com.ztesoft.report.utils.GeneralPathUtil;
import com.ztesoft.report.utils.excel.annotation.ExportConfig;
import com.ztesoft.report.utils.excel.convert.ExportConvert;
import com.ztesoft.report.utils.excel.handler.ExportHandler;
import com.ztesoft.report.utils.excel.pojo.ExportItem;
import com.ztesoft.report.utils.excel.pojo.Node;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel工具类
 *
 * @author y3tu
 * @date 2018/4/3
 */
@SuppressWarnings("ALL")
@Slf4j
public class ExcelUtils {

    private Class<?> mClass = null;
    private HttpServletResponse mResponse = null;
    /**
     * 分Sheet机制：每个Sheet最多多少条数据
     */
    @Value("${com.ztesoft.maxSheetRows}")
    private Integer mMaxSheetRecords = 10000;
    /**
     * 数据行数
     */
    private Integer dataNumber = 0;
    /**
     * 缓存数据格式器实例,避免多次使用反射进行实例化
     */
    private Map<String, ExportConvert> mConvertInstanceCache = new HashMap<String, ExportConvert>();

    protected ExcelUtils() {
    }

    protected ExcelUtils(Class<?> clazz) {
        this(clazz, null);
    }

    protected ExcelUtils(Class<?> clazz, HttpServletResponse response) {
        this.mResponse = response;
        this.mClass = clazz;
    }

    /**
     * 用于生成本地文件
     *
     * @param clazz 实体Class对象
     * @return ExcelUtils
     */
    public static ExcelUtils Builder(Class<?> clazz) {
        return new ExcelUtils(clazz);
    }

    /**
     * 用于浏览器导出
     *
     * @param clazz    实体Class对象
     * @param response 原生HttpServletResponse对象
     * @return ExcelUtils
     */
    public static ExcelUtils Export(Class<?> clazz, HttpServletResponse response) {
        return new ExcelUtils(clazz, response);
    }

    /**
     * 分Sheet机制：每个Sheet最多多少条数据(默认10000)
     *
     * @param size 数据条数
     * @return this
     */
    public ExcelUtils setMaxSheetRecords(Integer size) {
        this.mMaxSheetRecords = size;
        return this;
    }


    /**
     * 导出Excel(此方式需依赖浏览器实现文件下载,故应先使用$Export()构造器)
     *
     * @param fileName  文件名
     * @param sheetName 工作表名字
     * @return true-操作成功,false-操作失败
     */
    public ExcelDataExport toExcel(String fileName, int listCount, String sheetName, List<String> headConfig, String path) throws Exception {
        requireExportParams();
        try {
            return toExcel(fileName, listCount, sheetName, mResponse.getOutputStream(), headConfig,path);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 针对转换方法的默认实现(提供默认样式和文件命名规则)
     *
     * @param sheetName  工作表名字
     * @param out        输出流
     * @param headConfig 表头配置
     * @return true-操作成功,false-操作失败
     */
    public ExcelDataExport toExcel(String fileName, int listCount, String sheetName, OutputStream out, List<String> headConfig,String path) throws Exception {

        return toExcel(fileName, listCount, sheetName, new ExportHandler() {
            @Override
            public CellStyle headCellStyle(Workbook wb) {
                CellStyle cellStyle = wb.createCellStyle();
                Font font = wb.createFont();
                cellStyle.setFillForegroundColor((short) 12);
                // 填充模式
                // cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                // 上边框为细边框
                // cellStyle.setBorderTop(CellStyle.BORDER_THIN);
                // 右边框为细边框
                //cellStyle.setBorderRight(CellStyle.BORDER_THIN);
                // 下边框为细边框
                //cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
                // 左边框为细边框
                // cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
                // 对齐
                // cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
                cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
                cellStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
                // font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
                //font.setFontHeightInPoints((short) 11);// 字体大小
                font.setColor(HSSFColor.WHITE.index);
                // 应用标题字体到标题样式
                cellStyle.setFont(font);
                return cellStyle;
            }

            @Override
            public String exportFileName(String sheetName) {
                return String.format("导出-%s-%s", sheetName, System.currentTimeMillis());
            }
        }, out, headConfig,path);
    }


    public ExcelDataExport toExcel(String fileName, int listCount, String sheetName, ExportHandler handler, OutputStream out, List<String> headConfig,String path) throws Exception {
        requireBuilderParams();

        ExportConfig currentExportConfig = null;
        ExportItem currentExportItem = null;
        List<ExportItem> exportItems = new ArrayList<ExportItem>();

        for (Field field : mClass.getDeclaredFields()) {
            //获取excel表头的注解配置
            currentExportConfig = field.getAnnotation(ExportConfig.class);
            if (currentExportConfig != null) {
                currentExportItem = new ExportItem().setField(field.getName())
                        .setDisplay("field".equals(currentExportConfig.value()) ? field.getName() : currentExportConfig.value())
                        .setWidth(currentExportConfig.width()).setConvert(currentExportConfig.convert())
                        .setColor(currentExportConfig.color()).setReplace(currentExportConfig.replace());
                exportItems.add(currentExportItem);
            }
            currentExportItem = null;
            currentExportConfig = null;
        }

        //获取多级表头配置 start
        //选用LinkedHashMap，保证迭代顺序的一致性
        Map<String, Object> headMap = new LinkedHashMap<>();

        for (int i = 0 ; i< headConfig.size() ; i++) {
            String[] arr = headConfig.get(i).split("==");
            String config = arr[1];
            Map configMap = JSON.parseObject(config, Map.class);
            int width = Integer.valueOf(configMap.get("width").toString());
            String field = configMap.get("field").toString();
            String decimal="2";
            if (configMap.get("decimal")!=null){
                decimal= configMap.get("decimal").toString();
            }

            ExportItem exportItem = new ExportItem();
            exportItem.setField(field);
            exportItem.setDecimal(decimal);
            exportItems.add(exportItem);
            headMap.put(arr[0] + "@a@" + String.valueOf(i), configMap);
        }
        //迭代Map集合，并重构一套“根目录”
        Node root = new Node();
        for (Map.Entry<String, Object> entry : headMap.entrySet()) {
            root.addNodeByStringExpressionAndWidth(entry.getKey(), (Map<String, Object>) entry.getValue());
        }

        //遍历“根目录”
        List<Node> nodes = root.parseToSeqNodes();
        //获取树的深度
        Integer rootDeepLength = root.getDeepLength();
        //获取多级表头配置 end

        //创建新的工作薄
        //InputStream inputStream = new ClassPathResource("excel/tempexcel.xls").getInputStream();
        //File file = ResourceUtils.getFile("classpath:excel/tempexcel.xls");
        File file = new File(path);
        InputStream inputStream = new FileInputStream(file);

        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        //1.1 创建单元格样式
        HSSFCellStyle alignCenterStyle = workbook.createCellStyle();
        alignCenterStyle.setAlignment(HorizontalAlignment.CENTER);
        //表名样式：水平、垂直居中
        HSSFCellStyle font24Style = workbook.createCellStyle();
        font24Style.setAlignment(HorizontalAlignment.CENTER);
        font24Style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置背景色
        font24Style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        font24Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        font24Style.setBorderBottom(BorderStyle.THIN); //下边框
        font24Style.setBorderLeft(BorderStyle.THIN);//左边框
        font24Style.setBorderTop(BorderStyle.THIN);//上边框
        font24Style.setBorderRight(BorderStyle.THIN);//右边框

        font24Style.setWrapText(true);

        //24号字体
        HSSFFont font24 = workbook.createFont();
        font24.setFontName("黑体");
        font24.setFontHeightInPoints((short) 11);
        font24Style.setFont(font24);

        //计算出一共有多少个sheet页
        double sheetNo = Math.ceil((double) listCount / (double) mMaxSheetRecords);
        // =====多sheet生成填充数据=====
        int row = 0;
        for (int index = 0; index <= (sheetNo == 0.0 ? sheetNo : sheetNo - 1); index++) {
            HSSFSheet sheet = null;
            if (index == 0) {
                sheet = workbook.getSheetAt(0);
                workbook.setSheetName(0, sheetName);
            } else {
                sheet = workbook.cloneSheet(0);
                workbook.setSheetName(index, sheetName + "_" + index);
            }

            //创建表头
            row = 0;
            //3.2 在这个行中，创建一行单元格
            //以下是设置标题，和时间，先注释掉
/*            HSSFRow tableHeadRow = sheet.getRow(0);
            tableHeadRow.getCell(0).setCellValue(sheetName);
            HSSFRow tableHeadRow2 = sheet.getRow(1);
            tableHeadRow2.getCell(1).setCellValue(DateUtil.getDate());*/
            //tableHeadCell.setCellStyle(font24Style);
            row++;
            row++;
            row = sheet.getLastRowNum() + 1;
            HSSFRow[] rows = new HSSFRow[rootDeepLength];
            for (int i = 0; i < rows.length; i++) {
                rows[i] = sheet.createRow(row + i);
            }
            int[] columnIndexArr = new int[rootDeepLength];
            //4.2 遍历所有结点
            ReportServiceImpl reportService = new ReportServiceImpl();
            List<Map<String, Object>> element = reportService.getListMap(root);
            int coIndex = 0;
            for (Map<String, Object> map : element) {
                String label = map.get("label").toString();
                List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("children");
                if (list.size() > 0) {
                    int count = getChildCount(list);
                    int rowIndex = 0;
                    //从rowIndex = 1开始创建表头
                    createLevelHead(list, rowIndex + 1, rows, coIndex, font24Style, sheet, row);
                    //创建第一行的表头
                    rows[0].createCell(coIndex);
                    rows[0].getCell(coIndex).setCellValue(label);
                    rows[0].getCell(coIndex).setCellStyle(font24Style);
                    if (coIndex != coIndex + count - 1) {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, coIndex,
                                coIndex + count - 1);
                        sheet.addMergedRegion(cellRangeAddress);
                        POIUtils.setBorderForMergeCell(cellRangeAddress, sheet);
                    }
                    coIndex += count;
                } else {
                    rows[0].createCell(coIndex);
                    rows[0].getCell(coIndex).setCellValue(label);
                    rows[0].getCell(coIndex).setCellStyle(font24Style);
                    if (row != row + rows.length - 1) {
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row + rows.length - 1, coIndex, coIndex);
                        sheet.addMergedRegion(cellRangeAddress);
                        POIUtils.setBorderForMergeCell(cellRangeAddress, sheet);
                    }
                    coIndex++;
                }
            }
            MergedRegion(sheet,row,row+rootDeepLength-1);
            System.out.println(row);
            System.out.println(row+rootDeepLength);
            row += rows.length;
            System.out.println("合并单元格行数："+sheet.getNumMergedRegions());
            System.out.println("总列数:"+sheet.getRow(0).getPhysicalNumberOfCells());
            System.out.println("总行数:"+sheet.getLastRowNum());
            //表头的数据应该是很多单元格的合并、居中
            //四个参数：开始行，结束行，开始列，结束列
            //因为上面加了1，这里还要抵消掉
            /*CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, columnIndexArr[columnIndexArr.length - 1] - 1);
            sheet.addMergedRegion(cellRangeAddress);
            POIUtils.setBorderForMergeCell(cellRangeAddress, sheet);*/
            //添加
        }
        ExcelDataExport excelDataExport = new ExcelDataExport();
        excelDataExport.setWorkbook(workbook);
        excelDataExport.setExportItems(exportItems);
        excelDataExport.setmResponse(mResponse);
        excelDataExport.setHandler(handler);
        excelDataExport.setSheetName(sheetName);
        excelDataExport.setOut(out);
        excelDataExport.setRow(row);
        excelDataExport.setExcelUtils(this);
        return excelDataExport;

    }

    /**
     * 创建Excel
     *
     * @param mResponse
     * @param handler
     * @param sheetName
     * @param workbook
     * @param out
     */
    public static void createExcel(HttpServletResponse mResponse, ExportHandler handler,
                                   String sheetName, HSSFWorkbook workbook, OutputStream out) {
        try {
            // 生成Excel文件并下载.(通过response对象是否为空来判定是使用浏览器下载还是直接写入到output中)
            POIUtils.writeByLocalOrBrowser(mResponse, handler.exportFileName(sheetName), workbook, out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param data
     * @param exportItems
     * @param workbook
     * @param rowNumberIndex 目前进行的数据条数
     * @param row
     * @param startIndex     从第几个List索引开始获取值
     */
    public void addExcelRow(List<?> data, List<ExportItem> exportItems, HSSFWorkbook workbook, int rowNumberIndex,
                            int row, int startIndex, int listCount) throws Exception {
        int sheetIndex = rowNumberIndex / mMaxSheetRecords;
        if (rowNumberIndex >= listCount) {
            return;
        }
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Row bodyRow;
        String cellValue;
        Cell cell;
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        style.setFont(font);
        //内容加边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //表格格式化工具
        DataFormat format = workbook.createDataFormat();
        // 产生数据行
        if (data != null && data.size() > 0) {
            int startNo = rowNumberIndex % mMaxSheetRecords;
            //int endNo = Math.min(startNo + data.size(), mMaxSheetRecords);

            for (int i = startIndex; i < data.size(); i++) {
                bodyRow = POIUtils.newRow(sheet, startNo + row);
                startNo++;
                for (int j = 0; j < exportItems.size(); j++) {
                    // 处理单元格值
                    System.out.println("显示是否小数点："+exportItems.get(j).getDecimal());
                    cellValue = exportItems.get(j).getReplace();
                    if (StringUtils.isBlank(cellValue)) {
                        try {
                            cellValue = BeanUtils.getProperty(data.get(i), exportItems.get(j).getField());
                            if (StringUtils.isNotBlank(cellValue))
                                cellValue = "\t" + cellValue;
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }

                    // 格式化单元格值
                    if (StringUtils.isNotBlank(exportItems.get(j).getConvert())) {
                        cellValue = convertCellValue(cellValue, exportItems.get(j).getConvert());
                    }

                    // 单元格宽度
                    // POIUtils.setColumnWidth(sheet, j, exportItems.get(j).getWidth(), cellValue);

                    cell = POIUtils.newCell(bodyRow, j);
                    //判断是否是数字类型，是的话以数值显示在表格中，并且加上千分位逗号隔开
                    if (cellValue.replace("\t", "").matches("^(-?\\d+)(\\.\\d+)?")) {
                        cellValue = cellValue.replace("\t", "");
                        System.out.println(cellValue + "是数字");
                        DecimalFormat decimalFormat = null;
                        //&&"true".equalsIgnoreCase(exportItems.get(j).getDecimal())
                        String decimal =exportItems.get(j).getDecimal().trim();
                        switch(decimal){
                            case "0":
                                decimalFormat = new DecimalFormat("#,##0");
                                cell.setCellValue("".equals(cellValue) ? null : decimalFormat.format(Double.parseDouble(cellValue)));
                                break;
                            case "1":
                                decimalFormat = new DecimalFormat("#,##0.0");
                                cell.setCellValue("".equals(cellValue) ? null : decimalFormat.format(Double.parseDouble(cellValue.toString())));
                                break;
                            case "2":
                                style.setDataFormat(format.getFormat("#,##0.00"));
                                cell.setCellValue("".equals(cellValue) ? null : (Double.parseDouble(cellValue.toString())));
                                break;
                            case "3":
                                decimalFormat = new DecimalFormat("#,##0.000");
                                cell.setCellValue("".equals(cellValue) ? null : decimalFormat.format(Double.parseDouble(cellValue.toString())));
                                break;
                            case "4":
                                decimalFormat = new DecimalFormat("#,##0.0000");
                                cell.setCellValue("".equals(cellValue) ? null : decimalFormat.format(Double.parseDouble(cellValue.toString())));
                                break;
                            case "5":
                                decimalFormat = new DecimalFormat("#,##0.00000");
                                cell.setCellValue("".equals(cellValue) ? null : decimalFormat.format(Double.parseDouble(cellValue.toString())));
                                break;
                            default :style.setDataFormat(format.getFormat("#,##0.00"));
                                cell.setCellValue("".equals(cellValue) ? null : (Double.parseDouble(cellValue.toString())));
                                break;
                        }
                        //style.setDataFormat(format1);
                        cell.setCellStyle(style);
                    } else {
                        cell.setCellStyle(style);
                        // fix: 当值为“”时,当前index的cell会失效
                        cell.setCellValue("".equals(cellValue) ? null : cellValue);
                    }
                }
                rowNumberIndex++;
                //说明满上限了 需要翻页
                if (rowNumberIndex % mMaxSheetRecords == 0) {
                    addExcelRow(data, exportItems, workbook, rowNumberIndex, row, ++i, listCount);
                    return;
                }
            }
        }
        //印章
/*
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        bufferImg = ImageIO.read(new ClassPathResource("img/印章.png").getInputStream());
        ImageIO.write(bufferImg, "png", byteArrayOut);
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //这是插入EXCEL的位置,由于最小只能为0
        short col1 = 0;
        if (exportItems.size() >= 2) {
            col1 = (short) (exportItems.size() - 2);
        } else {
            col1 = (short) exportItems.size();
        }
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, rowNumberIndex + 6, (short) (exportItems.size()), rowNumberIndex + 12);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
*/
    }

    /**
     * 创建多级表头
     *
     * @param list        表头对象
     * @param rowIndex    行索引
     * @param rows        所有行数
     * @param coIndex     列索引
     * @param font24Style Excel样式
     * @param sheet       Excel表单
     * @param row         创建表头之前的行数
     */
    private int createLevelHead(List<Map<String, Object>> list, int rowIndex, HSSFRow[] rows,
                                int coIndex, HSSFCellStyle font24Style, HSSFSheet sheet, int row) {
        int rowIn = rowIndex;
        int coIn = coIndex;
        //该index代表同一行中的列索引到了哪里
        int index = coIndex;
        for (Map<String, Object> map1 : list) {
            String label = map1.get("label").toString();
            List<Map<String, Object>> list1 = (List<Map<String, Object>>) map1.get("children");
            //说明有子节点
            if (getChildCount(list1) > 0) {
                //递归 因为创建下一行需要rowIn + 1传入创建, 而列索引是每一行都从头开始创建
                //因为每列的索引不能单单通过index来 把coIn变化 所以需要temp-index来获取该次需要推进几个索引
                int temp = index;
                temp = createLevelHead(list1, rowIn + 1, rows, temp, font24Style, sheet, row);
                //下层全部创建完了，创建本层，需要合并列,但是不用合并行因为还有子节点
                rows[rowIn].createCell(coIn);
                rows[rowIn].getCell(coIn).setCellValue(label);
                rows[rowIn].getCell(coIn).setCellStyle(font24Style);
                if (coIn != (coIn + getChildCount(list1) - 1)) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(row + rowIn, row + rowIn,
                            coIn, coIn + getChildCount(list1) - 1);
                    sheet.addMergedRegion(cellRangeAddress);
                    POIUtils.setBorderForMergeCell(cellRangeAddress, sheet);
                }
                coIn += (temp - index);
                index = temp;
            } else {
                //没有子节点就不用合并列
                rows[rowIn].createCell(coIn);
                rows[rowIn].getCell(coIn).setCellValue(label);
                rows[rowIn].getCell(coIn).setCellStyle(font24Style);
                //因为第一行有数据
                if (row + rowIn != row + rows.length - 1) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(row + rowIn, row + rows.length - 1,
                            coIn, coIn);
                    sheet.addMergedRegion(cellRangeAddress);
                    POIUtils.setBorderForMergeCell(cellRangeAddress, sheet);
                }
                coIn++;
                //如果后面的节点包含子节点，子节点的索引也需要往后推一步
                index++;
            }
        }
        return coIn;
    }

    private int getChildCount(List<Map<String, Object>> list) {
        int o = 0;
        for (Map<String, Object> map1 : list) {
            String lable = map1.get("label").toString();
            List<Map<String, Object>> list1 = (List<Map<String, Object>>) map1.get("children");
            if (list1.size() == 0) {
                o++;
            } else {
                //递归
                o += getChildCount(list1);
            }
        }
        return o;
    }

    /**
     * 导出Csv
     *
     * @param data
     * @param path
     * @return
     */
    public boolean toCsv(List<?> data, String path, List<ExportItem> exportItemList, boolean displayHeader) {
        try {
            requireBuilderParams();
            if (data == null || data.size() < 1) {
                return false;
            }

            // 导出列查询。
            ExportConfig currentExportConfig = null;
            ExportItem currentExportItem = null;
            List<ExportItem> exportItems = new ArrayList<ExportItem>();
            for (Field field : mClass.getDeclaredFields()) {

                currentExportConfig = field.getAnnotation(ExportConfig.class);
                if (currentExportConfig != null) {
                    currentExportItem = new ExportItem().setField(field.getName())
                            .setDisplay("field".equals(currentExportConfig.value()) ? field.getName()
                                    : currentExportConfig.value())
                            .setConvert(currentExportConfig.convert()).setReplace(currentExportConfig.replace());
                    exportItems.add(currentExportItem);
                }

                currentExportItem = null;
                currentExportConfig = null;
            }
            exportItems.addAll(exportItemList);

            String cellValue;
            FileOutputStream out = new FileOutputStream(path);
            // 解决乱码
            out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
            CsvWriter csvWriter = new CsvWriter(out, ',', Charset.forName("UTF-8"));
            List<Object> csvHeaders = new ArrayList<Object>();
            for (int i = 0; i < exportItems.size(); i++) {
                csvHeaders.add(exportItems.get(i).getDisplay());
            }
            String[] csvHeadersArr = csvHeaders.toArray(new String[csvHeaders.size()]);
            if (displayHeader) {
                csvWriter.writeRecord(csvHeadersArr);
            }
            for (int i = 0; i < data.size(); i++) {
                List<Object> csvContent = new ArrayList<Object>();
                for (int j = 0; j < exportItems.size(); j++) {
                    // 处理单元格值
                    cellValue = exportItems.get(j).getReplace();
                    if (!StringUtils.isNotBlank(cellValue)) {
                        try {
                            cellValue = BeanUtils.getProperty(data.get(i), exportItems.get(j).getField());
                            if (StringUtils.isNotBlank(cellValue))
                                cellValue = "\t" + cellValue;
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }

                    // 格式化单元格值
                    if (StringUtils.isNotBlank(exportItems.get(j).getConvert())) {
                        cellValue = convertCellValue(cellValue, exportItems.get(j).getConvert());
                    }
                    if (cellValue == null) {
                        cellValue = "";
                    }
                    csvContent.add(cellValue);
                }
                String[] csvContentArr = csvContent.toArray(new String[csvContent.size()]);
                csvWriter.writeRecord(csvContentArr, false);
            }
            csvWriter.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 对值进行转换处理 支持两种方式
     * convertCellValue: number to String (beta)
     */
    private String convertCellValue(Object oldValue, String format) {
        try {
            String protocol = format.split(":")[0];

            // 键值对字符串解析：s:1=男,2=女
            if ("s".equalsIgnoreCase(protocol)) {

                String[] pattern = format.split(":")[1].split(",");
                for (String p : pattern) {
                    String[] cp = p.split("=");
                    if (cp[0].equals(oldValue)) {
                        return cp[1];
                    }
                }

            }
            if ("c".equalsIgnoreCase(protocol)) {

                String clazz = format.split(":")[1];
                ExportConvert export = mConvertInstanceCache.get(clazz);
                if (export == null) {
                    export = (ExportConvert) Class.forName(clazz).newInstance();
                    mConvertInstanceCache.put(clazz, export);
                }

                if (mConvertInstanceCache.size() > 10) {
                    mConvertInstanceCache.clear();
                }

                return export.handler(oldValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return String.valueOf(oldValue);
    }

    /**
     * 检查生成excel的必须参数是否存在
     */
    private void requireBuilderParams() {
        if (mClass == null) {
            throw new IllegalArgumentException("请先使用ExcelUtils.Builder(Class<?>)构造器初始化参数。");
        }
    }

    /**
     * 检查导出excel的必须参数是否存在
     */
    private void requireExportParams() {
        if (mClass == null || mResponse == null) {
            throw new IllegalArgumentException("请先使用ExcelUtils.Export(Class<?>, HttpServletResponse)构造器初始化参数。");
        }
    }

    public static void mergeSheetAllRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {//合并单元格
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    /**
     * 合并EXCEL，多Sheet页
     */
    public static void copyCellStyle(HSSFCellStyle fromStyle, HSSFCellStyle toStyle) {

        toStyle.cloneStyleFrom(fromStyle);//此一行代码搞定
        //下面统统不用
        /*
        //对齐方式
        toStyle.setAlignment(fromStyle.getAlignment());
        //边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
        //背景和前景
        //toStyle.setFillPattern(fromStyle.getFillPattern());  //填充图案，不起作用，转为黑色
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());  //不起作用
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
        toStyle.setDataFormat(fromStyle.getDataFormat());  //数据格式
        //toStyle.setFont(fromStyle.getFont());  //不起作用
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());//首行缩进
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());//旋转
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());  //垂直对齐
        toStyle.setWrapText(fromStyle.getWrapText()); //文本换行
        */
    }

    /**
     * 合并EXCEL，多Sheet页
     */
    public static void copyCell(HSSFWorkbook wb, HSSFCell fromCell, HSSFCell toCell) {
        HSSFCellStyle newstyle=wb.createCellStyle();
        copyCellStyle(fromCell.getCellStyle(), newstyle);
        //toCell.setEncoding(fromCell.getEncoding());
        //样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        int fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {
            toCell.setCellValue(fromCell.getNumericCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {
            // nothing21
        } else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else { // nothing29
        }

    }

    /**
     * 合并EXCEL，多Sheet页
     * @param wb
     * @param oldRow
     * @param toRow
     */
    public static void copyRow(HSSFWorkbook wb,HSSFRow oldRow,HSSFRow toRow){
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
            HSSFCell tmpCell = (HSSFCell) cellIt.next();
            HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb,tmpCell, newCell);
        }
    }

    /**
     * 合并EXCEL，多Sheet页
     */
    public static void copySheet(HSSFWorkbook wb,HSSFSheet fromSheet, HSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        //设置列宽
        for(int i=0;i<=fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();i++){
            toSheet.setColumnWidth(i,fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
            HSSFRow oldRow = (HSSFRow) rowIt.next();
            HSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb,oldRow,newRow);
        }
    }

    /**
     * 行复制功能
     *
     * @param fromRow
     * @param toRow
     */
    public static void copyRow(HSSFWorkbook wb, HSSFSheet sheet,HSSFSheet endSheet,HSSFRow fromRow, HSSFRow toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());
        for(int i = 0; i < endSheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = endSheet.getMergedRegion(i);
            if(cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() +
                        (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress
                        .getFirstColumn(), cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }
        for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            HSSFCell tmpCell = (HSSFCell) cellIt.next();
            HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell, copyValueFlag);
        }

    }

    /**
     * 复制单元格
     *
     * @param srcCell
     * @param distCell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyCell(HSSFWorkbook wb, HSSFCell srcCell, HSSFCell distCell,
                                boolean copyValueFlag) {
        HSSFCellStyle newstyle = wb.createCellStyle();
        newstyle.cloneStyleFrom(srcCell.getCellStyle());
        //样式
        distCell.setCellStyle(newstyle);
        //评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        // 不同数据类型处理
        int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (copyValueFlag) {
            if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
                // nothing21
            } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            } else { // nothing29
            }
        }
    }

    /**
     * 测试合并列
     * @param sheet
     */
    private static void mergeColumns(HSSFSheet sheet) {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();

        Row row = null;
        Cell cell1 = null;
        Cell cell2 = null;

        int colSpan = 0;

        for (int r = 4; r < rowsCount; r++) {
            row = sheet.getRow(r);
            // 重置
            colSpan = 0;
            row = sheet.getRow(r);
            for (int c = 0; c < colsCount; c++) {
                cell1 = row.getCell(c);
                cell2 = row.getCell(c + 1);
                if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                    if (c == colsCount - 1) {
                        break;
                    } else {
                        continue;
                    }
                }
                if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                    if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        break;
                    }
                }

                if (cell1 != null && cell2 != null) {
                    // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                    if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                        colSpan++;
                    } else {
                        // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                        if (colSpan >= 1) {
                            // 合并行中连续相同的值的单元格
                            sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                            // 合并后重置colSpan
                            colSpan = 0;
                            continue;
                        }
                    }
                }

            }
        }

    }

    /**
     * 获取sheet中的合并单元格
     * @param sheet
     * @return
     */
    public static Map getNumMergedRegions(HSSFSheet sheet){
        Map map=new HashMap(4);
        List<Integer>  firstColumns=new LinkedList<>();
        List<Integer>  lastColumns=new LinkedList<>();
        List<Integer>  firstRows=new LinkedList<>();
        List<Integer>  lastRows=new LinkedList<>();
        //得到所有的合并单元格
        int numMergedRegions = sheet.getNumMergedRegions();
        for (int i=0;i<numMergedRegions;i++){
            //取出排序为i的合并单元格
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            //起始列
            int firstColumn = mergedRegion.getFirstColumn();
            //结束列
            int lastColumn = mergedRegion.getLastColumn();
            //起始行
            int firstRow = mergedRegion.getFirstRow();
            //结束行
            int lastRow = mergedRegion.getLastRow();

            firstColumns.add(firstColumn);
            lastColumns.add(lastColumn);
            firstRows.add(firstRow);
            lastRows.add(lastRow);
        }
        map.put("firstColumns",firstColumns);
        map.put("lastColumns",lastColumns);
        map.put("firstRows",firstRows);
        map.put("lastRows",lastRows);
        return map;
    }

    /**
     * 合并单元格表头  列合并
     * @param sheet
     */
    public static  void MergedRegion(HSSFSheet sheet,int firstRow,int lastRow){
        Map numMergedRegions = getNumMergedRegions(sheet);
        //起始列，结束列，起始行，结束行

        //总列数
        int coloumNum=sheet.getRow(firstRow).getPhysicalNumberOfCells();
        for (int i=0;i<coloumNum;i++){
            int mergeCol=0;
            for (int j = firstRow; j <lastRow; j++) {
                boolean ins = GeneralPathUtil.isIns(i, j, numMergedRegions);
                if (ins){
                    System.out.println("continue");
                    continue;
                }

                HSSFCell cell = sheet.getRow(j).getCell(i);
                String replace="";
                if (cell==null){
                //if (cell==null||cell.getStringCellValue()==""){
                    continue;
                }else {
                    replace = StrUtil.replace(cell.getStringCellValue(), "\r\n\t", "").trim();
                }

                HSSFCell cell1 = sheet.getRow(j + 1).getCell(i);
                String replace2="";
                if (cell1!=null){
               // if (cell1!=null&&cell1.getStringCellValue()!=""){
                    replace2 = StrUtil.replace(cell1.getStringCellValue(), "\r\n\t", "").trim();
                }
                System.out.println("j:"+j+"=replace:"+replace+",j+1:"+(j+1)+"=replace2:"+replace2);
                if (cell1==null){
                    if (mergeCol>0){
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(j-mergeCol, j, i, i);
                        System.out.println("cell1==null 合并区域{"+(j-mergeCol)+","+j+","+i+","+i+"}");
                        sheet.addMergedRegion(cellRangeAddress);
                        mergeCol=0;
                    }
                }
                if (replace.equals(replace2)){
                    mergeCol++;
                }else{
                    if (mergeCol>0){
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(j-mergeCol, j, i, i);
                        System.out.println("replace!=replace2 合并区域{"+(j-mergeCol)+","+j+","+i+","+i+"}");
                        sheet.addMergedRegion(cellRangeAddress);
                        mergeCol=0;
                    }
                }

            }
        }




    }

    /*    public static void main(String[] args) throws Exception{
                FileInputStream inStream = null;
                FileInputStream inStream2 = null;
                try {
                inStream = new FileInputStream(new File("C:/Users/ASUS/Desktop/tempexcel.xls"));
                inStream2 = new FileInputStream(new File("C:/Users/ASUS/Desktop/endtemp.xls"));
                Workbook wb = WorkbookFactory.create(inStream);
                Workbook wb2 = WorkbookFactory.create(inStream2);
                Sheet s2 = wb2.getSheetAt(0);
                for (Row r2:s2) {
                    boolean nullFlag = true;
                    for (Cell c:r2) {
                        if (c != null && c.toString() != "") {
                            nullFlag = false;
                        }
                    }
                    if (nullFlag) {
                        continue;
                    }
                    Sheet sheet = wb.getSheetAt(0);
                    Row row = sheet.createRow(sheet.getLastRowNum()+2);
                    copyRow((HSSFWorkbook) wb, (HSSFSheet) sheet, (HSSFRow) r2, (HSSFRow) row, true);
                }
                //sheet.addMergedRegion(new CellRangeAddress(1,3,4,4));
    *//*            Row row1 = sheet.getRow(sheet.getLastRowNum());
            Row row = sheet.createRow(sheet.getLastRowNum()+1);
            Cell cell=row.createCell(0);
            cell.setCellValue("制表部门");
            cell=row.createCell(2);
            cell.setCellValue("审核单位领导");*//*
            FileOutputStream os;
            os = new FileOutputStream("C:/Users/ASUS/Desktop/tempexcel.xls");
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (inStream != null) {
                    inStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }*/

/*    public static void main(String[] args) throws Exception{
        List<String> fileNameList = new ArrayList<>();
        fileNameList.add("tempexcel.xls");
        fileNameList.add("endtemp.xls");
        String tempUrl = "C:/Users/ASUS/Desktop";
        //将所有类型的尽调excel文件合并成一个excel文件
        HSSFWorkbook newExcelCreat = new HSSFWorkbook();
        for(String fromExcelName:fileNameList) {//遍历每个源excel文件，fileNameList为源文件的名称集合
            InputStream in = new FileInputStream(tempUrl+ "/" +fromExcelName);
            HSSFWorkbook fromExcel = new HSSFWorkbook(in);
            for(int i = 0; i < fromExcel.getNumberOfSheets(); i++) {//遍历每个sheet
                HSSFSheet oldSheet = fromExcel.getSheetAt(i);
                HSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                ExcelUtils.copySheet(newExcelCreat, oldSheet, newSheet);
            }
        }
        String allFileName=tempUrl +"/"+ "合并后的.xls";
        FileOutputStream fileOut = new FileOutputStream(allFileName);
        newExcelCreat.write(fileOut);
        fileOut.flush();
        fileOut.close();
        //删除各个源文件
        for(String fromExcelName:fileNameList) {//遍历每个源excel文件
            File file=new File(tempUrl+File.separator+fromExcelName);
            if(file.exists()){
                file.delete();
            }
        }
    }*/

}
